﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using VX.DBTransfer.Framework;

namespace VX.DBTransfer.DAL
{
    public class DALBase : IDisposable
    {

        private int m_intStatus = 0;
        private string m_strMessage = "";
        private SQLDBAccess m_DBAccess;
        private StringBuilder m_strSQL = new StringBuilder();
        public bool IsOpen = true;
        public bool IsSuccess;
        private IList<DataParameter> m_Parameters = null;
        private enmDBDriver m_DBDriver;

        private bool m_notCatchException = false;
        private bool m_blnDBReady = false;

        /// <summary>
        /// 供普通BLL构造或者ServiceStatus任务构造。
        /// </summary>
        public DALBase()
        {

            m_DBDriver = enmDBDriver.SQLServer;
            m_Parameters = null;
            IsSuccess = false;
        }
        public DALBase(string conn)
        {
            this.ConnString = conn;
            m_DBDriver = enmDBDriver.SQLServer;
            m_Parameters = null;
            IsSuccess = false;
        }

        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        private string connString;

        public string ConnString
        {
            get
            {
                return connString;
            }
            set
            {
                connString = value;
            }
        }

        private SQLDBAccess GetDB()
        {
            string strDBCnn = string.Empty;
            if (m_blnDBReady)
            {
                strDBCnn = ConnString;
            }
            ClearParameters();
            ClearSQL();
            if (m_DBAccess == null)
            {
                m_strSQL.Remove(0, m_strSQL.Length);
                m_DBAccess = new SQLDBAccess(m_DBDriver);

                //增加是否捕捉异常处理
                if (!m_notCatchException)
                {
                    m_DBAccess.NotCatchException = false;
                }

                if (!m_DBAccess.Open(strDBCnn))
                {
                    m_strMessage = m_DBAccess.Message;
                    m_DBAccess.Dispose();
                    m_DBAccess = null;
                    IsOpen = false;
                }
            }
            if (m_DBAccess != null)
            {
                if (!m_DBAccess.IsOpen())
                {
                    if (!m_DBAccess.Open(strDBCnn))
                    {
                        m_strMessage = m_DBAccess.Message;
                        m_DBAccess.Dispose();
                        m_DBAccess = null;
                        IsOpen = false;
                    }
                }
            }
            return m_DBAccess;
        }

        public SQLDBAccess DBInstance
        {
            get
            {
                m_blnDBReady = true;
                return GetDB();
            }
            set
            {
                m_DBAccess = value;
            }
        }

        public void AddParameter(string parameterName,
                                 DataType dbType,
                                 ParameterDirection direction,
                                 object Value)
        {
            if (m_Parameters == null)
            {
                m_Parameters = new List<DataParameter>();
            }

            DataParameter para = new DataParameter(parameterName, dbType);
            para.Direction = direction;
            para.Value = Value;
            m_Parameters.Add(para);
        }

        public IList<DataParameter> Parameters
        {
            get
            {
                if (m_Parameters == null) m_Parameters = new List<DataParameter>();
                return m_Parameters;
            }
        }

        public void AddParameter(string parameterName,
                                 DataType dbType,
                                 int size,
                                 ParameterDirection direction,
                                 object Value)
        {
            if (m_Parameters == null)
            {
                m_Parameters = new List<DataParameter>();
            }

            DataParameter para = new DataParameter(parameterName, dbType, size);
            para.Direction = direction;
            para.Value = Value;
            m_Parameters.Add(para);
        }

        public void AddParameter(string parameterName,
                                 DataType dbType,
                                 object Value)
        {
            AddParameter(parameterName,
                         dbType,
                         ParameterDirection.Input,
                         GetNull(Value));
        }
        private object GetNull(object o)
        {
            return o == null ? DBNull.Value : o;
        }
        public void AddParameter(string parameterName,
                                 int size,
                                 object Value)
        {
            if (m_Parameters == null)
            {
                m_Parameters = new List<DataParameter>();
            }

            DataParameter para = new DataParameter(parameterName, DataType.NVarChar, size);
            para.Direction = ParameterDirection.Input;
            para.Value = Value;
            m_Parameters.Add(para);
        }

        public void ClearParameters()
        {
            if (m_Parameters != null)
            {
                m_Parameters.Clear();
                m_Parameters = null;
            }
        }


        public void ClearSQL()
        {
            SQL.Remove(0, SQL.Length);
        }

        public object GetParameter(int iIndex)
        {
            if (m_Parameters != null && m_Parameters.Count >= iIndex)
            {
                return m_Parameters[iIndex].Value;
            }
            else
            {
                return null;
            }
        }

        public object GetParameter(string strParaName)
        {
            DataParameter oTemp;

            if (m_Parameters != null)
            {
                if (m_DBDriver == enmDBDriver.Oracle) strParaName = strParaName.TrimStart('@').TrimStart(':');
                for (int i = 0; i < m_Parameters.Count; i++)
                {
                    oTemp = m_Parameters[i];
                    if (oTemp.ParameterName == strParaName)
                    {
                        if (oTemp.Value == DBNull.Value)
                        {
                            switch (oTemp.Type)
                            {
                                case DataType.Bit:
                                case DataType.Float:
                                case DataType.Int:
                                case DataType.Money:
                                case DataType.Real:
                                case DataType.SmallInt:
                                case DataType.TinyInt:
                                    return 0;
                                case DataType.DateTime:
                                    return new DateTime(1900, 1, 1);
                                default:
                                    return "";
                            }
                        }
                        else
                        {
                            return oTemp.Value;
                        }
                    }
                }

                return null;
            }
            else
            {
                return null;
            }
        }

        public DataParameter GetParameterObject(string strParaName)
        {
            DataParameter oTemp;
            if (m_Parameters != null)
            {
                if (!strParaName.StartsWith("@")) strParaName = "@" + strParaName;
                for (int i = 0; i < m_Parameters.Count; i++)
                {
                    if (m_Parameters[i].ParameterName == strParaName.ToLower())
                    {
                        oTemp = m_Parameters[i];
                        return oTemp;
                    }
                }
                return null;
            }
            else
            {
                return null;
            }
        }

        public int ExecSP()
        {
            //m_strMessage = "";
            SQLDBAccess oTemp = m_DBAccess;
            oTemp.SQL = SQL.ToString();

            int iReturn = oTemp.ExecSP(ref m_Parameters);

            IsSuccess = oTemp.IsSuccess;


            if (IsSuccess)
            {
                return iReturn;
            }
            else
            {
                m_strMessage = oTemp.Message;
                m_intStatus = -1;
                return m_intStatus;
            }
        }

        public DataTable GetDataTableSP()
        {
            //m_strMessage = "";
            SQLDBAccess oTemp = m_DBAccess;
            oTemp.SQL = SQL.ToString();

            DataTable dtReturn = oTemp.GetDataTableSP(ref m_Parameters);

            IsSuccess = oTemp.IsSuccess;

            if (IsSuccess)
            {
                m_intStatus = 0;
                return dtReturn;
            }
            else
            {
                m_strMessage = oTemp.Message;
                m_intStatus = -1;
                return null;
            }
        }

        public DataSet GetDataSetSP()
        {
            //m_strMessage = "";
            SQLDBAccess oTemp = m_DBAccess;
            oTemp.SQL = SQL.ToString();

            DataSet dsReturn = oTemp.GetDataSetSP(ref m_Parameters);


            IsSuccess = oTemp.IsSuccess;

            if (IsSuccess)
            {
                m_intStatus = 0;
                return dsReturn;
            }
            else
            {
                m_strMessage = oTemp.Message;
                m_intStatus = -1;
                return null;
            }
        }

        public DataSet GetDataSet()
        {
            //m_strMessage = "";
            SQLDBAccess oTemp = m_DBAccess;
            oTemp.SQL = SQL.ToString();

            DataSet dtReturn = oTemp.GetDataSet(ref m_Parameters);

            IsSuccess = oTemp.IsSuccess;



            if (IsSuccess)
            {
                m_intStatus = 0;
                return dtReturn;
            }
            else
            {
                m_strMessage = oTemp.Message;
                m_intStatus = -1;

                return null;
            }
        }


        public DataTable GetDataTable()
        {
            return GetDataTable("");
        }

        public DataTable GetDataTable(string TableName)
        {
            return GetDataTable(TableName, "");
        }

        private DataTable GetDataTable(string TableName, string strCurrentCompany)
        {
            //m_strMessage = "";
            SQLDBAccess oTemp = m_DBAccess;
            oTemp.SQL = SQL.ToString();


            DataTable dtReturn = oTemp.GetDataTable(ref m_Parameters);

            IsSuccess = oTemp.IsSuccess;



            if (IsSuccess)
            {
                m_intStatus = 0;
                if (TableName != "")
                {
                    dtReturn.TableName = TableName;
                }

                return dtReturn;
            }
            else
            {
                m_strMessage = oTemp.Message;
                m_intStatus = -1;

                return null;
            }
        }

        public DbDataReader GetReader()
        {
            //m_strMessage = "";
            SQLDBAccess oTemp = m_DBAccess;
            oTemp.SQL = m_strSQL.ToString();

            DbDataReader drReturn = oTemp.GetReader(ref m_Parameters);

            IsSuccess = oTemp.IsSuccess;



            if (IsSuccess)
            {
                m_intStatus = 0;
                return drReturn;
            }
            else
            {
                m_strMessage = oTemp.Message;
                m_intStatus = -1;

                return null;
            }
        }

        private void CheckStatementForUpdate(string strStatement)
        {
            strStatement = strStatement.Substring(6).Trim();
            int i, j;
            string strUpdateNextWord = "";			//Update后的第一个词
            string strFromNextWord = "";			//From后的第一个词
            string strFromNextNextWord = "";		//From后的第一个词后的别名（要考虑AS）
            i = strStatement.IndexOf(" ");
            if (i > 0) strUpdateNextWord = strStatement.Substring(0, i).Trim();
            i = strStatement.IndexOf(" from ");
            if (i > 0)
            {
                string[] arrTmp = strStatement.Substring(i + 6).Trim().Split(' ');
                j = 0;
                for (i = 0; i < arrTmp.Length; i++)
                {
                    if (arrTmp[i].Trim() != "")
                    {
                        if (j == 0)
                        {
                            strFromNextWord = arrTmp[i].Trim();
                        }
                        else if (j <= 2)
                        {
                            if (arrTmp[i].Trim() != "as")
                            {
                                strFromNextNextWord = arrTmp[i].Trim();
                            }
                            break;
                        }
                        j++;
                    }
                }
            }

        }

        public int ExecSQL()
        {
            //m_strMessage = "";
            SQLDBAccess oTemp = m_DBAccess;
            oTemp.SQL = m_strSQL.ToString();

            int iReturn = oTemp.ExecSQL(ref m_Parameters);

            IsSuccess = oTemp.IsSuccess;



            if (IsSuccess)
            {

                m_intStatus = 0;
                return iReturn;
            }
            else
            {
                m_strMessage = oTemp.Message;
                m_intStatus = -1;

                return m_intStatus;
            }
        }

        public object ExecuteScalar()
        {
            SQLDBAccess oTemp = m_DBAccess;
            oTemp.SQL = m_strSQL.ToString();

            object returnScalar = oTemp.ExecuteScalar(ref m_Parameters);

            IsSuccess = oTemp.IsSuccess;



            if (IsSuccess)
            {

                m_intStatus = 0;
                return returnScalar;
            }
            else
            {
                m_strMessage = oTemp.Message;
                m_intStatus = -1;

                return null;
            }
        }

        public int GetRecordCount()
        {
            //m_strMessage = "";
            SQLDBAccess oTemp = m_DBAccess;
            oTemp.SQL = SQL.ToString();

            DataTable dtReturn = oTemp.GetDataTable(ref m_Parameters);

            IsSuccess = oTemp.IsSuccess;



            if (IsSuccess)
            {
                m_intStatus = 0;
                return dtReturn.Rows.Count;
            }
            else
            {
                m_strMessage = oTemp.Message;
                m_intStatus = -1;

                return m_intStatus;
            }
        }

        public bool BeginTran()
        {
            SQLDBAccess oTemp = m_DBAccess;
            oTemp.BeginTran();
            IsSuccess = oTemp.IsSuccess;

            if (IsSuccess)
            {
                return true;
            }
            else
            {
                m_strMessage = oTemp.Message;

                return false;
            }
        }

        public void Commit()
        {
            m_DBAccess.Commit();
        }

        public void Rollback()
        {
            m_DBAccess.Rollback();
        }

        public void ProcessLog(int ProcessID, int IndentLevel, string LogID, string[] LogArgs)
        {
            if (ProcessID > 0)
            {
                string strLog = (IndentLevel != 0 ? IndentLevel.ToString() + "-" : "") + LogID;
                if (LogArgs != null && LogArgs.Length > 0)
                {
                    for (int i = 0; i < LogArgs.Length; i++)
                    {
                        strLog += "|" + LogArgs[i];
                    }
                }
                m_strSQL.Remove(0, m_strSQL.Length);
                m_strSQL.Append("declare @strLog nvarchar(1000);");
                m_strSQL.Append("set @strLog = '" + strLog + "';");
                m_strSQL.Append("exec hrp_Process_SaveLog " + ProcessID.ToString() + ",@strLog;");
                ExecSQL();
            }
        }

        public void Dispose()
        {
            m_strSQL.Remove(0, m_strSQL.Length);

            if (m_Parameters != null)
            {
                m_Parameters.Clear();
                m_Parameters = null;
            }
            if (m_DBAccess != null)
            {
                //m_DBAccess.Dispose(m_blnNewOpen);
                m_DBAccess.iConnection.Close();
                m_DBAccess.Dispose();
                m_DBAccess = null;
            }
        }

        public StringBuilder SQL
        {
            get
            {
                return m_strSQL;
            }
            set
            {
                m_strSQL = value;
            }
        }
        public bool NotCatchException
        {
            set { m_notCatchException = value; }
        }
        public string Message
        {
            get
            {
                return m_strMessage;
            }
            set
            {
                m_strMessage = value;
            }
        }
        public int Status
        {
            get { return m_intStatus; }
            set { m_intStatus = value; }
        }

        public int DB2Int(object Value)
        {
            try
            {
                if (Value is DBNull || Value.ToString() == "")
                {
                    return 0;
                }
                else
                {
                    return Convert.ToInt32(Value);
                }
            }
            catch
            {
                return 0;
            }
        }

        public double DB2Double(object Value)
        {
            try
            {
                if (Value is DBNull || Value.ToString() == "")
                {
                    return 0;
                }
                else
                {
                    return Convert.ToDouble(Value);
                }
            }
            catch
            {
                return 0;
            }
        }

        public string DB2String(object Value)
        {
            try
            {
                if (Value is DBNull)
                {
                    return string.Empty;
                }
                else
                {
                    return Value.ToString().TrimEnd();
                }
            }
            catch
            {
                return string.Empty;
            }
        }

        public bool DB2Boolean(object Value)
        {
            bool result;
            try
            {
                if (Value is DBNull)
                {
                    result = false;
                }
                else
                {
                    if (Value.ToString().ToLower() == "1")
                    {
                        result = true;
                    }
                    else if (Value.ToString().ToLower() == "0")
                    {
                        result = false;
                    }
                    else
                    {
                        result = Convert.ToBoolean(Value);
                    }
                }
            }
            catch
            {
                result = false;
            }

            return result;
        }

        public DateTime DB2DateTime(object Value)
        {
            try
            {
                if (Value is DBNull)
                {
                    return new DateTime(1900, 1, 1);
                }
                else
                {
                    return Convert.ToDateTime(Value);
                }
            }
            catch
            {
                return new DateTime(1900, 1, 1);
            }
        }

        public DataType GetDataType(FieldType ft)
        {
            DataType dType;
            switch (ft)
            {
                case FieldType.Int:
                    dType = DataType.Int;
                    break;
                case FieldType.Float:
                    dType = DataType.Float;
                    break;
                case FieldType.NText:
                    dType = DataType.NText;
                    break;
                case FieldType.Boolean:
                    dType = DataType.Bit;
                    break;
                case FieldType.DateTime:
                    dType = DataType.DateTime;
                    break;
                default:
                    dType = DataType.NVarChar;
                    break;
            }
            return dType;
        }

        public DataType GetDataType(System.Type type)
        {
            DataType dType = DataType.NVarChar;
            if (type == typeof(System.String))
            {
                dType = DataType.NVarChar;
            }
            else if (type == typeof(System.DateTime))
            {
                dType = DataType.DateTime;
            }
            else if (type == typeof(System.Boolean))
            {
                dType = DataType.Bit;
            }
            else if (type == typeof(System.Double))
            {
                dType = DataType.Float;
            }
            else if (type == typeof(System.Single))
            {
                dType = DataType.Real;
            }
            else if (type == typeof(System.Byte))
            {
                dType = DataType.TinyInt;
            }
            else if (type == typeof(System.Int16))
            {
                dType = DataType.SmallInt;
            }
            else if (type == typeof(System.Int32))
            {
                dType = DataType.Int;
            }
            else if (type == typeof(System.Int64))
            {
                dType = DataType.BigInt;
            }
            else if (type == typeof(System.Decimal))
            {
                dType = DataType.Money;
            }
            return dType;
        }

        public string GetExcelType(FieldType ft)
        {
            string dType = "nvarchar";
            if (ft == FieldType.DateTime)
            {
                dType = "datetime";
            }
            else if (ft == FieldType.Boolean)
            {
                //dType = "bit";
            }
            else if (ft == FieldType.Int)
            {
                dType = "int";
            }
            else if (ft == FieldType.Float)
            {
                dType = "float";
            }
            return dType;
        }

        public string GetExcelType(System.Type type)
        {
            string dType = "nvarchar";
            if (type == typeof(System.DateTime))
            {
                dType = "datetime";
            }
            else if (type == typeof(System.Boolean))
            {
                dType = "bit";
            }
            else if (type == typeof(System.Double))
            {
                dType = "float";
            }
            else if (type == typeof(System.Single))
            {
                dType = "float";
            }
            else if (type == typeof(System.Decimal))
            {
                dType = "float";
            }
            else if (type == typeof(System.Byte))
            {
                dType = "int";
            }
            else if (type == typeof(System.Int16))
            {
                dType = "int";
            }
            else if (type == typeof(System.Int32))
            {
                dType = "int";
            }
            else if (type == typeof(System.Int64))
            {
                dType = "int";
            }
            return dType;
        }

    }

    public interface IRowMapper<T>
    {
        T MapRow(IDataReader reader, int rowNum);
    }
}
